""" DigitalICS: mobile data collection tool to complete surveys with integrated multimedia

    Copyright (C) 2009. Yael Schwartzman  

    This program is free software: you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by
    the Free Software Foundation, either version 3 of the License, or
    (at your option) any later version.

    This program is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
    GNU General Public License for more details.

    You should have received a copy of the GNU General Public License
    along with this program.  If not, see <http://www.gnu.org/licenses/>
    
    Contact information: Yael Schwartzman -  yaelsf@gmail.com
    """

from api.fexplorer import FExplorer

class db:
    def __init__(self,debug = False):
        self.debug = debug
        fe = FExplorer()
        self.db_path = fe.get_db_path()
        self.db_contents_file = fe.get_database_contents_file()
        self.db_table_file = fe.get_database_import_file()
        self.db_community_file= fe.get_database_communities_file()
        if debug:
            self.debug_file = open("test.txt","w")
            self.populate_communities()
            #self.populate()
            
        else:
            import e32db
            self.db = e32db.Dbms()
            self.dbv = e32db.Db_view()
            #try:
            #    self.db.open(unicode(self.db_path))
            #except:
            self.db.create(unicode(self.db_path))
            self.db.open(unicode(self.db_path))
            self.populate_communities()
            #self.populate()
            
    def populate(self):
        f = open(self.db_table_file,"r")
        contents = f.read()
        f.close()
        table_list = contents.split("%")
        for table in table_list:
            if self.debug:
                self.debug_file.write(table+"\n")
            else:
                self.query(table)
    
        f = open(self.db_contents_file,"r")
        contents = f.read()
        f.close()
        tables = contents.split("next")
        for table in tables:
            self.populate_table(table)
        
    def populate_communities(self):
        f = open(self.db_community_file,"r")
        contents = f.read()
        f.close()
        query = u"CREATE TABLE communities (id COUNTER, community  VARCHAR ,evaluator COUNTER)"
        #query = "CREATE TABLE communities (community  VARCHAR)"
        if self.debug:
            self.debug_file.write(query+"\n")
        else:
            self.query(query)
        lines = contents.split("\n")
        #self.db.execute(u"INSERT INTO communities(community) VALUES 'SAN'")
        for line in lines:
            line_contents = line.split(";")
            query = u"INSERT INTO communities(id,community,evaluator) VALUES (%d,'%s',%d)" % (int(line_contents[0]),line_contents[1],int(line_contents[2]))
            #query = "INSERT INTO communities (community) VALUES '%s'" %line_contents[1]
            if self.debug:
                self.debug_file.write(query+"\n")
            else:
                self.query(query)
            
    def populate_table(self,contents):
        contents = contents.replace("\n","")
        contents = contents.replace("\r","")
        contents = contents.replace("\t","")
        lines = contents.split("%%")
        name = lines[0]
        self.debug_file.write("name %s"%name)
        cols = lines[1]
        cols_str = cols.replace('"',"")
        cols_list = cols_str.split(";")
        query = " INSERT INTO %s(%s) VALUES " %(name,",".join(cols_list))
        for i in range(2,len(lines)):
            if len(lines[i]) > 0:
                values = lines[i].split(";")
                if self.debug:
                    self.debug_file.write(query+" (%s)" %",".join(values))
                else:
                    self.query(query+" (%s)" %",".join(values))

    def query(self, sql):
            #print "sql %s" %sql
            self.db.execute(sql)

    def get_results(self,sql):
        self.dbv.prepare(self.db, unicode(sql))
        self.dbv.first_line()
        results = [] 
        for i in range(self.dbv.count_line()):
            self.dbv.get_line()
            results.append([self.dbv.col(1),self.dbv.col(2),self.dbv.col(3)])
            self.dbv.next_line()
        return results
        
    def evaluacion_check_query(self,CLAVE_ORG):
        query = """ SELECT check_value from evaluacion_check WHERE CLAVE_ORG = 
                '%s' ORDER BY check_id""" % (CLAVE_ORG)
        checks = self.get_results(query)
        return_str = [ check for check in checks] 
        return ",".join(return_str)
    
    def evaluacion_query(self,CLAVE_ORG):
        query = """ SELECT OBSERVACIONES from evaluacion WHERE CLAVE_ORG = '%s'""" % (CLAVE_ORG)
        observaciones = self.get_results(query)
        return observaciones.encode('latin-1')
    
    def get_communities(self):
        query = """SELECT community FROM communities"""
        results = self.get_results(query)
        return results 
    
    def get_recomendaciones(self,CLAVE_ORG):
        return self.evaluacion_query(CLAVE_ORG) + self.evaluacion_check_query(CLAVE_ORG)
    
    def close(self):
        self.db.close() 
    
if __name__=="__main__":
     test_db = db(debug=True)
     test_db.debug_file.close()
     